package com.dd;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.log4j.Logger;

import com.chantel.tool.db.DBConnMgr;
import com.chantel.tool.db.DBUtil;
import com.dd.exception.UnDealParameterTypeException;

/**
 * 扩展DBUtil 提供更强大的数据库操作
 * 
 * @author shaoguowei
 * 
 */
public class DBUtil2 extends DBUtil {
	private static final Logger logger = Logger.getLogger(	DBUtil2.class.getName());
	private static DBUtil2 _instance = new DBUtil2();

	public DBUtil2() {
		// do nothing
	}

	public static DBUtil2 getDefaultDBUtil2() {
		return _instance;
	}

	public Map getRow(String prefixSql, Map whereFields) {
		return getRow(defaultDB, prefixSql, whereFields);
	}

	public Map getRow(String pool, String prefixSql, Map whereFields) {
		Connection conn = DBConnMgr.getConnection(pool);
		Map map;
		try {
			map = getRow(conn, prefixSql, whereFields);
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return map;
	}

	/**
	 * 参数查询
	 * 
	 * @param conn
	 * @param prefixSql
	 * @param whereFields
	 * @return
	 */
	public Map getRow(Connection conn, String prefixSql, Map whereFields) {
		logger
				.debug("enter the getRow(Connection conn, String prefixSql, Map whereFields method");
		Map m_rowData = null;
		PreparedStatement pstmt = null;
		ResultSet result = null;
		String sql = null;

		try {
			if (whereFields == null || whereFields.size() < 1) {
				sql = prefixSql;
				pstmt = conn.prepareStatement(sql);
			} else {
				Set s = whereFields.keySet();
				Iterator it = s.iterator();
				int i = 0;
				StringBuffer sb = new StringBuffer(" where ");
				Object[] obj = new Object[whereFields.size()];
				while (it.hasNext()) {
					Object key = it.next();
					obj[i] = whereFields.get(key);

					if (key != null) {
						if (i != 0)
							sb.append(" and ");
						sb.append(key).append(" = ? ");
						i++;
					}
				}
				sql = prefixSql + sb.toString();

				pstmt = conn.prepareStatement(sql);
				for (int j = 0; j < obj.length; j++) {
					try {
						setParameter(pstmt, obj[j], j + 1);
					} catch (UnDealParameterTypeException e) {
						e.printStackTrace();
						logger.error("system error:" + e.getMessage());
					}
				}
			}
			logger.debug("getrow sql:" + sql);
			result = pstmt.executeQuery();
			if (result.next())
				m_rowData = rowToMap(result);
			result.close();
			result = null;
			pstmt.close();
			pstmt = null;
			return m_rowData;

			// logger.info("sql:"+sql);

		} catch (SQLException e) {
			e.printStackTrace();
			System.out.print(e.toString());
		}
		if (result != null) {
			try {
				result.close();
			} catch (Exception exception) {
			}
			result = null;
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (Exception exception1) {
			}
			pstmt = null;
		}
		return null;
	}

	public List getRecords(String prefixSql, Map whereFields) {
		return getRecords(defaultDB, prefixSql, whereFields);
	}

	public List getRecords(String pool, String prefixSql, Map whereFields) {
		Connection conn = DBConnMgr.getConnection(pool);
		List list;
		try {
			list = getRecords(conn, prefixSql, whereFields);
		} finally {
			try {
				conn.close();
			} catch (Exception e) {
			}
		}
		return list;
	}

	public List getRecords(Connection conn, String prefixSql, Map whereFields) {
		logger
				.debug("enter the getRecords(Connection conn, String prefixSql, Map whereFields method");
		List rs = new ArrayList();
		PreparedStatement pstmt = null;
		ResultSet result = null;
		String sql = null;

		try {
			if (whereFields == null || whereFields.size() < 1) {
				sql = prefixSql;
				pstmt = conn.prepareStatement(sql);
			} else {
				Set s = whereFields.keySet();
				Iterator it = s.iterator();
				int i = 0;
				StringBuffer sb = new StringBuffer(" where ");
				Object[] obj = new Object[whereFields.size()];
				while (it.hasNext()) {
					Object key = it.next();
					obj[i] = whereFields.get(key);

					if (key != null) {
						if (i != 0)
							sb.append(" and ");
						sb.append(key).append(" = ? ");
						i++;
					}
				}

				sql = prefixSql + sb.toString();

				pstmt = conn.prepareStatement(sql);
				for (int j = 0; j < obj.length; j++) {
					try {
						setParameter(pstmt, obj[j], j + 1);
					} catch (UnDealParameterTypeException e) {
						e.printStackTrace();
						logger.error("system error:" + e.getMessage());
					}
				}
			}
			logger.debug("sql1:" + sql);
			for (result = pstmt.executeQuery(); result.next(); rs
					.add(rowToMap(result))) {
			}
			result.close();
			result = null;
			pstmt.close();
			pstmt = null;
			return rs;

		} catch (SQLException e) {
			System.out.print(e.toString());
			e.printStackTrace();
		}
		if (result != null) {
			try {
				result.close();
			} catch (Exception exception) {
			}
			result = null;
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (Exception exception1) {
			}
			pstmt = null;
		}
		return null;
	}

	public void setParameter(PreparedStatement pstmt, Object parameter,
			int parameterIndex) throws SQLException,
			UnDealParameterTypeException {
		if (parameter instanceof java.lang.String)
			pstmt.setString(parameterIndex, (String) parameter);
		else if (parameter instanceof java.lang.Integer)
			pstmt.setInt(parameterIndex, ((Integer) parameter).intValue());
		else if (parameter instanceof java.lang.Float)
			pstmt.setFloat(parameterIndex, ((Float) parameter).floatValue());
		else if (parameter instanceof java.util.Date
				|| parameter instanceof java.sql.Date)
			pstmt.setDate(parameterIndex, (java.sql.Date) parameter);
		else if (parameter instanceof java.lang.Long)
			pstmt.setLong(parameterIndex, ((Long) parameter).longValue());
		else if (parameter instanceof java.lang.Object)
			pstmt.setObject(parameterIndex, parameter);
		else
			throw new UnDealParameterTypeException(
					"未被支持的参数类型，在DBUtil2.java setParameter()中增加");
	}
}
